1. Technical Field
This invention relates to a method and system for restoring a table in a database. More specifically, the invention relates to extracting a base table from a database backup with minimal cost.
2. Description of the Prior Art
Large modern database management systems are commonly found in terabyte sizes on systems containing hundreds of CPUs and thousands of spindles. These systems also contain tens of thousands of database storage objects, such as relational tables and their associated access structures, including indexes, materialized views, and system catalogs. Today's databases are commonly backed up on recordable storage media, which may include CD-Roms, disks, and magnetic tape. In general, it is common practice to back up changes to the database on a daily basis, and to back up the entire database on a weekly basis.
Typically, a database includes catalog tables and base tables. The catalog tables store data that describes base tables. The base table is a table within the database that stores operator accessible and identifiable data. The data stored in the catalog table is not readily visible to an operator of the database. Rather the data stored in the catalog table pertains to meta-data. In the case of a database, the meta-data is in the catalog table and describes operator visible attributes of the base table, such as the names and types of columns. In addition to the catalog and base tables, the database includes an extent list which describes the physical location of pages of the catalog and base tables within the database. The extent list is stored in a special internal table which is not visible to operators of the database. This internal table that houses the extent list is known as the directory table. Accordingly, the directory table, catalog table, and base table function in a relational format to enable efficient use of data stored in the database.
A backup copy of the database usually holds only pages of data that were in use between the prior backup and the current backup, as these pages are the most likely to contain data that may have been subject to change. FIG. 1 is a flow chart (10), illustrating one implementation of a prior art process of creating a backup copy of a database and storing the backup copy on a recordable storage media. The operator of the database produces a hardcopy of the database schema for all tables in the database (12). Thereafter, the operator produces a hardcopy of the table identifiers for all tables in the database schema (14). Table identifiers are integers internal to the database that identify individual tables therein. Following creation of the hard copies at steps (12) and (14), the operator may request a backup operation of the database (16). The backup operation will copy pages of the directory table to the backup storage media (18). The directory table is the first table reproduced in the backup of the database because it contains information pertaining to the physical location of the base tables of the database. Following the backup of the directory table, the backup operation will copy all of the remaining pages of the database to the backup storage media (20). Accordingly, the prior art method for creating a backup of the database requires production of a hardcopy of both the database schema as well as the table identifiers for the tables in the database.
Once the operator performs a backup of the database, it may become necessary at some point in time to restore one or more designated base tables from the backup. FIG. 2A is a flow chart (50) illustrating one prior art implementation of a first part of a process of restoring one or more designated base tables from a backup copy of the database. The operator requiring the table restore manually creates an empty base table with the schema of the base table to be restored (52), i.e. attributes of the table. The schema of the designated base table is obtained from the hardcopy that was produced at steps (12) and (14) during the backup of the database. The operator then requests a restore of the designated base table by providing the table identifier of the base table to restore (54). Following the operator initiated input at steps (52) and (54), a proceeding page of directory table are read from the backup storage media of the database (56). As indicated during the backup process (10), the table identifiers are internal database identifiers that identify tables within the database. A test is conducted to determine if the page of table identifiers read at step (56) describes the designated base table to be restored (58). A negative response to the test at step (58) will result in a return to step (56) for a reading of the proceeding page of table identifiers from the backup storage media. This process continues until a positive response is received from the test at step (58). Once a positive response is received, a list of physical pages identifying the location for the designated base table is extracted (60) and the remaining pages of the directory table may be passed over for review (62). Accordingly, the first part of the base table restoration process involves identifying the location of the physical pages of data provided by the table identifiers.
Once the location of the designated base table information has been located, the designated base table restoration process extracts the designated base table data from the specified locations in the storage media. FIG. 2B is a prior art flow chart (70) illustrating the steps in completing the restoration process. A test is conducted to determine if the data extraction process has reached the end of the storage media (72). A positive response to the test at step (72) is an indication that the end of the storage media has been reached (74). However, with a negative response to the test at step (72), the process continues with reading the next page of the backup database (76). Thereafter, a test is conducted to determine if the page read at step (76) included data pertaining to the designated base table to be restored (78). A negative response to the test at step (78) will return to step (72). However, a positive response to the test at step (78) will cause an extraction of a row of data from the page of the backup storage media (80), which will be inserted into the designated base table (82) created at step (52). In general, the extracted data may include one or more rows of data. As such, following step (82), a test is conducted to determine if there is more than one row of data on the specified page of the backup tape (84). A positive response to the test at step (84) will cause a return to step (80) as this is an indication that there are a plurality of rows of designated base table data in the specified page of the backup storage media. However, a negative response to the test at step (84) will cause a return to step (72) to determine whether the identification and extraction process has reached the end of the backup storage media. Accordingly, the completion of the table restore process extracts one or more rows of designated base table data into the empty base table created at step (52).
The prior art process of restoring a designated base table from a database requires manual intervention for creation of the schema associated with the designated base table. However, in a large database system, generation and use of a hardcopy of the table schema is not efficient. In addition, restoration of a base table requires manual configuration of the schema of the base table by an operator as well as a prior identification of the table identifier. Accordingly, what is desirable is an efficient and effective method and system for backing up a database and subsequently restoring a base table based upon meta-data.